﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DBhelper
    {
        /// <summary>
        /// 连接字符串从配置文件中读取
        /// </summary>
        //private readonly static string CONNSTRING = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        private readonly static string CONNSTRING = "Data Source=.;Initial Catalog=exam;Integrated Security=True";

        /// <summary>
        ///  执行增删改操作方法
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="paras"></param>
        public static bool GetExecuteNonQuery(string sqlstr, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {

                try
                {
                    SqlCommand cmd = new SqlCommand(sqlstr, conn);
                    cmd.Parameters.AddRange(paras);
                    conn.Open();
                    return cmd.ExecuteNonQuery() > 0;

                }
                catch (SqlException e)
                {

                    throw new Exception(e.Message);
                }
            }

        }

        /// <summary>
        ///  用户表老师表导入
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="paras"></param>
        public static bool UsersTeacherAdd(string UserSql, string TeacherSql)
        {
            using (SqlConnection con = new SqlConnection(CONNSTRING))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("UsersTeachersImport", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] para = { 
                                      new SqlParameter("@UserSql", SqlDbType.NVarChar),
                                      new SqlParameter("@TeacherSql", SqlDbType.NVarChar)
                                      };
                    para[0].Value = UserSql;
                    para[1].Value = TeacherSql;
                    cmd.Parameters.AddRange(para);

                    return cmd.ExecuteNonQuery() > 0;

                }
                catch (SqlException e)
                {

                    throw new Exception(e.Message);
                }
            }

        }

        /// <summary>
        /// 执行检索查询方法
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sqlstr, params  SqlParameter[] paras)
        {
            try
            {
                SqlConnection conn = new SqlConnection(CONNSTRING);
                SqlCommand cmd = new SqlCommand(sqlstr, conn);
                cmd.Parameters.AddRange(paras);
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (SqlException e)
            {

                throw new Exception(e.Message);
            }
        }

        /// <summary>
        ///   注：CommandBehavior.CloseConnection表示关闭SqlDataReader的同时把连接也关掉
        ///返回第一行第一列的方法 
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetExecuteScalar(string sqlstr, params  SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {

                try
                {
                    SqlCommand cmd = new SqlCommand(sqlstr, conn);
                    cmd.Parameters.AddRange(paras);
                    conn.Open();
                    return cmd.ExecuteScalar();

                }
                catch (OleDbException e)
                {

                    throw new Exception(e.Message);
                }
            }
        }


        /// <summary>
        /// 返回DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramters"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string sql, params SqlParameter[] paramters)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {

                try
                {
                    DataSet ds = new DataSet();
                    SqlCommand command = new SqlCommand(sql, conn);
                    command.Parameters.AddRange(paramters);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(ds);
                    return ds.Tables[0];

                }
                catch (OleDbException e)
                {

                    throw new Exception(e.Message);
                }
            }
        }



    }
}
